﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CDB.Model.Colunas;
using CDB.Model.Tabelas;
using CDB.DataLayer.Colunas;
using CDB.DataLayer.Tabelas;
using CDB.DataLayer.Utilitarios;
using CDB.Model.Utilitarios;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections.ObjectModel;
using System.Xml;



namespace CDB.BusinessLayer.Utilitarios
{
    public class BLUtilitarios
    {
        #region Propriedades
        private string StringConnection { get; set; }
        private string Provider { get; set; }
        #endregion

        #region Construtor
        public BLUtilitarios(string pstrConnection, string pstrProvider)
        {
            this.StringConnection = pstrConnection;
            this.Provider = pstrProvider;
        }
        #endregion

        #region Métodos
        #region Gerar Procedure
        public string GerarProcedures(string pstrNomeTabela)
        {
            BLGeradorProcedure BLGeradorProc = new BLGeradorProcedure(this.StringConnection, this.Provider);
            StringBuilder strScript = new StringBuilder();

            strScript.AppendLine(BLGeradorProc.GerarProcInserir(pstrNomeTabela));
            strScript.AppendLine(BLGeradorProc.GerarProcDelete(pstrNomeTabela));
            strScript.AppendLine(BLGeradorProc.GerarProcUpdate(pstrNomeTabela));
            strScript.AppendLine(BLGeradorProc.GerarProcListar(pstrNomeTabela));

            return strScript.ToString();
        }
        #endregion

        #region GetServers
        static public List<ServerBD> GetServers()
        {
            List<ServerBD> lstServerDB = new List<ServerBD>();
            SqlDataSourceEnumerator Servidores = SqlDataSourceEnumerator.Instance;
            DataTable dtlServers = Servidores.GetDataSources();

            foreach (DataRow objRow in dtlServers.Rows)
	        {
		        ServerBD objServerDB = new ServerBD();
                objServerDB.FromDataRow(objRow);

                lstServerDB.Add(objServerDB);
	        }

            return lstServerDB;

        }
        #endregion

        #region GetStringConexao
        static public string GetStringConexao(string pstrDataSource,string pstrInitialCatalog,string pstrUserName,string pstrPassword)
        {
            SqlConnectionStringBuilder objConStringBuilder = new SqlConnectionStringBuilder();
            objConStringBuilder.DataSource = pstrDataSource;
            objConStringBuilder.InitialCatalog = pstrInitialCatalog;
            objConStringBuilder.IntegratedSecurity = true;
            objConStringBuilder.UserID = pstrUserName;
            objConStringBuilder.Password = pstrPassword;

            return objConStringBuilder.ConnectionString;
        }
        #endregion

        #region GetBancos
        public Collection<string> ListarBancos()
        {
            var objDLUtilitarios = new DLUtilitarios(this.StringConnection,this.Provider);
            return objDLUtilitarios.ListarBancos();  
        }

        #endregion

        #region CreateXMLFile
        /// <summary>
        ///     Cria o arquivo xml quando não encontrado
        /// </summary>
        /// <param name="pstrPath">Path onde será criado</param>
        /// <history>
        ///     rsilveira 04/11/2011 Created
        /// </history>
        /// <returns>true/false</returns>
        private static bool CreateXMLDB(string pstrPath)
        {
            try
            {
                XmlDocument doc = new XmlDocument();
            
                XmlNode docNode = doc.CreateXmlDeclaration("1.0", "UTF-8", null);
                doc.AppendChild(docNode);

                //Cria o elemento pai
                docNode = doc.CreateElement("coneccao");
                doc.AppendChild(docNode);
            
                doc.Save(pstrPath);

                return true;
            }
            catch (Exception)
            {                
                return false;
            }
        }
        #endregion

        #region InsertXMLDB
        /// <summary>
        ///     Insere um registro no arquivo xml
        /// </summary>
        /// <history>
        ///     rsilveria 04/11/2011
        /// </history>
        /// <param name="pstrPath">Path do arquivo</param>
        /// <param name="pstrBase">Base</param>
        /// <param name="pstrUserName">UserName</param>
        /// <param name="pstrPassword">Password</param>
        /// <param name="pstrDataSource">DataSource</param>
        /// <returns>true/false</returns>
        public static bool InsertXMLDB(string pstrPath,string pstrBase,string pstrUserName,string pstrPassword,string pstrDataSource)
        {
            if (!System.IO.File.Exists(pstrPath))
            {
                if (!BLUtilitarios.CreateXMLDB(pstrPath))
                    return false;
            }

            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.Load(pstrPath);

            XmlNode Linha = xmlDoc.CreateElement("banco");

            XmlNode Base = xmlDoc.CreateElement("base");
            XmlNode UserName = xmlDoc.CreateElement("username");
            XmlNode Password = xmlDoc.CreateElement("senha");
            XmlNode DataSource = xmlDoc.CreateElement("datasource");

            Base.InnerText = pstrBase;
            UserName.InnerText = pstrUserName;
            Password.InnerText = pstrPassword;
            DataSource.InnerText = pstrDataSource;

            Linha.AppendChild(Base);
            Linha.AppendChild(UserName);
            Linha.AppendChild(Password);
            Linha.AppendChild(DataSource);

            xmlDoc.SelectSingleNode("/coneccao").AppendChild(Linha);
            xmlDoc.Save(pstrPath);

            return true;
        }
        #endregion

        
        #endregion
    }        

    
     
}
